{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6e25ccfd",
   "metadata": {},
   "source": [
    "## Factor Calculation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "ef70c8b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import statsmodels.formula.api as smf\n",
    "import time\n",
    "import statsmodels.api as sm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7ebd707a",
   "metadata": {},
   "outputs": [],
   "source": [
    "Daily_return = pd.read_csv('Daily_data.csv')\n",
    "Daily_return = Daily_return.drop('Unnamed: 0', axis=1)\n",
    "Daily_return.index = Daily_return['Stkcd']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8c3ad131",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Minutes taken = 0.17687989870707194\n"
     ]
    }
   ],
   "source": [
    "# 将Daily_return的index修改为只保留年份和月份的交易日期\n",
    "trade_date = np.array(Daily_return['Trddt'])                # 将\"Trddt\"这列数据提取，转换为array\n",
    "date_list = []                                              # 设置个空列表，仅保留交易日期的年月，赋值到空列表中\n",
    "tic = time.time()\n",
    "for j in range(0,len(trade_date)):\n",
    "        date = trade_date[j][0:7]\n",
    "        date_list.append(date)\n",
    "df_date = pd.DataFrame(date_list)                           # 将交易日期列表转换为dataframe\n",
    "df_date.rename(columns={0:\"trade_month\"},inplace=True)      # 将列名设置为trade_month\n",
    "df_date = df_date                                           # 将重复的交易日期删除\n",
    "trade_month_list = df_date['trade_month'].tolist()\n",
    "Daily_return.index = trade_month_list\n",
    "toc = time.time()\n",
    "print(\"Minutes taken = \" + str((toc-tic)/60.0))   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f758555c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将股票代码前缺失的0补齐新生成一列数据，并替换原来的\"Stkcd\"\n",
    "stock_code_array = np.array(Daily_return['Stkcd']) # 提取每个月在交易的股票代码，将其先转换成array\n",
    "stock_code = stock_code_array.tolist() # 而后将array转换成list\n",
    "stk_code = [] \n",
    "for i in stock_code:\n",
    "    code = str(i).zfill(6)\n",
    "    stk_code.append(code)\n",
    "Daily_return['Stkcd'] = stk_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8073ea2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 获取交易月份数据\n",
    "trade_date = np.array(Daily_return['Trddt'].drop_duplicates()) # 将\"Trddt\"这列数据提取，剔除重复值，转换为array\n",
    "date_list = []                                              # 设置个空列表，仅保留交易日期的年月，赋值到空列表中\n",
    "for j in range(0,len(trade_date)):\n",
    "        date = trade_date[j][0:7]\n",
    "        date_list.append(date)\n",
    "df_date = pd.DataFrame(date_list)                           # 将交易日期列表转换为dataframe\n",
    "df_date.rename(columns={0:\"trade_month\"},inplace=True)      # 将列名设置为trade_month\n",
    "df_date = df_date.drop_duplicates()                         # 将重复的交易日期删除\n",
    "df_date = df_date.sort_values(by='trade_month',ascending=True) # 按照日期顺序排列整齐\n",
    "list_month = df_date['trade_month'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c8ab893",
   "metadata": {},
   "source": [
    "### Size_factor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "eda94035",
   "metadata": {},
   "outputs": [],
   "source": [
    "size = pd.read_csv('size.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "c753b84a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将股票代码前缺失的0补齐新生成一列数据，并替换原来的\"Stkcd\"\n",
    "stock_code_array = np.array(size['Stkcd']) # 提取每个月在交易的股票代码，将其先转换成array\n",
    "stock_code = stock_code_array.tolist() # 而后将array转换成list\n",
    "stk_code = [] \n",
    "for i in stock_code:\n",
    "    code = str(i).zfill(6)\n",
    "    stk_code.append(code)\n",
    "size['Stkcd'] = stk_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "dcf1240f",
   "metadata": {},
   "outputs": [],
   "source": [
    "size = size.sort_values(by='Trdmnt', ascending=True) # 按照时间排序\n",
    "list_month = size['Trdmnt'].drop_duplicates().tolist() # 提取交易月份数据\n",
    "size.index = size['Trdmnt'] # 将index修改为交易月份"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ced68754",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将交易月份依次替换成Group_number\n",
    "Size = pd.DataFrame()\n",
    "for i in range(0,len(list_month)):\n",
    "    df = size.loc[list_month[i]]\n",
    "    df.index = (i+1) * np.ones(len(df))\n",
    "    Size = pd.concat([Size,df],axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "886cb70a",
   "metadata": {},
   "outputs": [],
   "source": [
    "Size['Group'] = Size.index # 添加一列Group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "f5f37265",
   "metadata": {},
   "outputs": [],
   "source": [
    "Size.to_csv('Size_0.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cf6baf4",
   "metadata": {},
   "source": [
    "### MOM factor"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbb788b3",
   "metadata": {},
   "source": [
    "#### 数据预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "0fdc2123",
   "metadata": {},
   "outputs": [],
   "source": [
    "mom = pd.read_csv('TRD_Mnth_99_20.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "6a3ad13a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将股票代码前缺失的0补齐新生成一列数据，并替换原来的\"Stkcd\"\n",
    "stock_code_array = np.array(mom['Stkcd']) # 提取每个月在交易的股票代码，将其先转换成array\n",
    "stock_code = stock_code_array.tolist() # 而后将array转换成list\n",
    "stk_code = [] \n",
    "for i in stock_code:\n",
    "    code = str(i).zfill(6)\n",
    "    stk_code.append(code)\n",
    "mom['Stkcd'] = stk_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "9ce1b5c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "mom = mom.drop(['Volume','Market_value'], axis=1) # 删除'Volume','Market_value'列数据\n",
    "mom.index = mom['Stkcd'] # 将mom的index修改为\"Stkcd\"\n",
    "mom = mom.dropna() # 剔除存在缺失值的股票"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "71303cd2",
   "metadata": {},
   "outputs": [],
   "source": [
    "code = mom['Stkcd'].drop_duplicates().tolist() # 提取在交易的所有股票代码\n",
    "code.sort()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "5736dde5",
   "metadata": {},
   "outputs": [],
   "source": [
    " # 剔除月度样本个数<11的股票代码\n",
    "for i in code:\n",
    "    if mom.loc[i]['Mretwd'].size < 11:\n",
    "        code.remove(i)\n",
    "    else:None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "55d6e644",
   "metadata": {},
   "outputs": [],
   "source": [
    "#剔除月度样本个数<11的股票\n",
    "mom = mom.loc[code]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82271c0f",
   "metadata": {},
   "source": [
    "#### MOM factor 计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "750dddfc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Goal: 计算MOM因子，每只股票当月的MOM是过去11个月的累计回报率\n",
    "# Step1: 创建一个Big DataFrame,合并个股在时间序列的mom因子\n",
    "# Step2: 提取个股的月度回报率数据\n",
    "Cum_df = pd.DataFrame()\n",
    "tic = time.time()\n",
    "for i in range(0,len(code)-1):\n",
    "        print(i)\n",
    "        stk_data = mom.loc[code[i]]\n",
    "        stk_data.index = stk_data['Trdmnt'] # 将个股月度回报index修改为\"Trdmnt\"\n",
    "        trade_month = stk_data['Trdmnt'].tolist() # 提取个股的交易月份\n",
    "        trade_month.sort() # 对个股的交易月份进行排序\n",
    "        cum_code = stk_data['Stkcd'].tolist()[11:] # 提取个股mom因子的股票代码，用来和cum_ret进行匹配\n",
    "        cum_month = trade_month[11:] # 提取个股mom因子的时间序列，用来和cum_ret进行匹配\n",
    "        cum_df = pd.DataFrame()\n",
    "        cum_list = []\n",
    "        # Step3: 创建一个Small DataFrame,依次提取个股过去11个月的月度回报率并计算累计回报率，将所得回报率以及月份放入其中\n",
    "        for j in range(0,len(cum_month)):\n",
    "            ret_11 = stk_data.loc[trade_month[j:j+11]]['Mretwd'].tolist() # 在当月，提取过去11个月的月度回报率\n",
    "            cum_ret = np.sum(ret_11) # 计算当月的累计回报率\n",
    "            cum_list.append(cum_ret) # 将当月累计回报率放入cum_list中\n",
    "        cum_df['cum_ret'] = cum_list # 将cum_list和cum_month放入small dataframe\n",
    "        cum_df['month'] = cum_month\n",
    "        cum_df['Stkcd'] = cum_code\n",
    "        Cum_df = pd.concat([Cum_df,cum_df], axis=0)\n",
    "        # Step4: 将Small DataFrame依次与Big DataFrame合并\n",
    "toc = time.time()\n",
    "print(\"Minutes taken = \" + str((toc-tic)/60.0))   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "48c67b74",
   "metadata": {},
   "outputs": [],
   "source": [
    "Cum_df.to_csv('cum_return.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "067b5c90",
   "metadata": {},
   "source": [
    "### Beta factor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5d2ea98",
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "Rm_Rf = pd.read_excel('Rm_Rf.xlsx')\n",
    "Daily_data = pd.merge(Daily_return,Rm_Rf,on='Trddt') # 将日度收益率数据与Rm,Rf进行合并\n",
    "Daily_data = Daily_data.sort_values(by='Trddt',ascending=True) # 按照时间升序排列\n",
    "Daily_data.to_csv('Daily_data.csv')\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "54482d46",
   "metadata": {},
   "outputs": [],
   "source": [
    "Daily_return = pd.read_csv('Daily_data.csv')\n",
    "Daily_return = Daily_return.drop('Unnamed: 0', axis=1)\n",
    "Daily_return.index = Daily_return['Stkcd']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "059aa1a6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Minutes taken = 0.14651096264521282\n"
     ]
    }
   ],
   "source": [
    "# 将Daily_return的index修改为只保留年份和月份的交易日期\n",
    "trade_date = np.array(Daily_return['Trddt'])                # 将\"Trddt\"这列数据提取，转换为array\n",
    "date_list = []                                              # 设置个空列表，仅保留交易日期的年月，赋值到空列表中\n",
    "tic = time.time()\n",
    "for j in range(0,len(trade_date)):\n",
    "        date = trade_date[j][0:7]\n",
    "        date_list.append(date)\n",
    "df_date = pd.DataFrame(date_list)                           # 将交易日期列表转换为dataframe\n",
    "df_date.rename(columns={0:\"trade_month\"},inplace=True)      # 将列名设置为trade_month\n",
    "df_date = df_date                                           # 将重复的交易日期删除\n",
    "trade_month_list = df_date['trade_month'].tolist()\n",
    "Daily_return.index = trade_month_list\n",
    "toc = time.time()\n",
    "print(\"Minutes taken = \" + str((toc-tic)/60.0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "f56d4c29",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将股票代码前缺失的0补齐新生成一列数据，并替换原来的\"Stkcd\"\n",
    "stock_code_array = np.array(Daily_return['Stkcd']) # 提取每个月在交易的股票代码，将其先转换成array\n",
    "stock_code = stock_code_array.tolist() # 而后将array转换成list\n",
    "stk_code = [] \n",
    "for i in stock_code:\n",
    "    code = str(i).zfill(6)\n",
    "    stk_code.append(code)\n",
    "Daily_return['Stkcd'] = stk_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "165b61eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 获取交易月份数据\n",
    "trade_date = np.array(Daily_return['Trddt'].drop_duplicates()) # 将\"Trddt\"这列数据提取，剔除重复值，转换为array\n",
    "date_list = []                                              # 设置个空列表，仅保留交易日期的年月，赋值到空列表中\n",
    "for j in range(0,len(trade_date)):\n",
    "        date = trade_date[j][0:7]\n",
    "        date_list.append(date)\n",
    "df_date = pd.DataFrame(date_list)                           # 将交易日期列表转换为dataframe\n",
    "df_date.rename(columns={0:\"trade_month\"},inplace=True)      # 将列名设置为trade_month\n",
    "df_date = df_date.drop_duplicates()                         # 将重复的交易日期删除\n",
    "df_date = df_date.sort_values(by='trade_month',ascending=True) # 按照日期顺序排列整齐\n",
    "list_month = df_date['trade_month'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94870637",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Goal: 计算因子的月度时序数据\n",
    "Beta = []\n",
    "Reg_stk_code = []\n",
    "tic = time.time()\n",
    "for i in range(0,len(list_month)):\n",
    "    print(list_month[i])\n",
    "    # Step1: 按照月份依次将个股日度收益率提取出来\n",
    "    daily_data = Daily_return.loc[list_month[i]] # 提取个股当月的数据\n",
    "    month_code = daily_data['Stkcd'].drop_duplicates().tolist() # 提取当月参与交易的股票代码\n",
    "    daily_data.index = daily_data['Stkcd'] # 修改dataframe的index为股票代码\n",
    "    # Special step:根据文章的计算方法，计算个股每个月的beta值，并放入month_beta,同时将对应的股票代码放入mth_code\n",
    "    # 将每个月的month_beta，mth_code放入大List中，最后再合并成dataframe\n",
    "    month_beta = []\n",
    "    mth_code = []\n",
    "    # Step2: 提取当月的个股数据\n",
    "    for j in range(0,len(month_code)):\n",
    "        reg_df = [] # 将每只股票的回归数据dataframe放入reg_df中，跑回归时直接提取\n",
    "        daily_data_stk = daily_data.loc[month_code[j]] # 在某一个月内，按照month_code的顺序，提取个股数据\n",
    "        if daily_data_stk['Dretwd'].size >= 3: # 由于部分股票当月数据个数过少，故需要进行剔除样本个数<3的\n",
    "            Rm_Rf = daily_data_stk['Rm'] - daily_data_stk['Rf'] # 计算市场风险溢价Rm - Rf\n",
    "            y_df = daily_data_stk.iloc[1:-1] # 提取并计算被解释变量y\n",
    "            y = y_df['Dretwd'] - y_df['Rf']\n",
    "            x1 = Rm_Rf.iloc[0:-2] # 分别提取解释变量x1,x2,x3\n",
    "            x2 = Rm_Rf.iloc[1:-1]\n",
    "            x3 = Rm_Rf.iloc[2:]\n",
    "            reg_data = pd.concat([y,x1,x2,x3], axis=1) # 将回归数据拼成dataframe\n",
    "            reg_data.columns = ['y','x1','x2','x3'] # 修改列名\n",
    "            reg_df.append(reg_data)\n",
    "            mth_code.append(month_code[j]) # 将个股当月的beta因子对应的股票代码append到small list 中\n",
    "        else:None\n",
    "        # Step3: 计算每只个股在当月的beta值\n",
    "        for k in range(0,len(reg_df)): # 提取reg_df中的回归数据\n",
    "            reg_data = reg_df[k]\n",
    "            est = smf.ols(formula='y ~ x1 + x2 + x3', data=reg_data).fit() # 回归\n",
    "            list_params = est.params.tolist() # 获取回归系数\n",
    "            beta = np.mean(list_params[1:]) # 计算个股在当月的beta值\n",
    "            month_beta.append(beta) # 将beta值append到small list中\n",
    "    Beta.append(month_beta)\n",
    "    Reg_stk_code.append(mth_code)\n",
    "toc = time.time()\n",
    "print(\"Minutes taken = \" + str((toc-tic)/60.0))   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "148ecbd8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将beta数据整理成dataframe格式\n",
    "Beta_df = pd.DataFrame()\n",
    "for i in range(0,len(Beta)):\n",
    "    df = pd.DataFrame()\n",
    "    df['beta'] = Beta[i]\n",
    "    df['stk_code'] = Reg_stk_code[i]\n",
    "    df['Group'] = (i+1) * np.ones(len(Beta[i]))\n",
    "    Beta_df = pd.concat([Beta_df,df], axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8608f75c",
   "metadata": {},
   "source": [
    "### IVOL factor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0804c152",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Goal: 计算因子的月度时序数据\n",
    "IVOL = []\n",
    "Reg_stk_code = []\n",
    "tic = time.time()\n",
    "for i in range(0,len(list_month)):\n",
    "    print(list_month[i])\n",
    "    # Step1: 按照月份依次将个股日度收益率提取出来\n",
    "    daily_data = Daily_return.loc[list_month[i]] # 提取当月的所有个股数据\n",
    "    month_code = daily_data['Stkcd'].drop_duplicates().tolist() # 提取当月参与交易的股票代码\n",
    "    daily_data.index = daily_data['Stkcd'] # 修改dataframe的index为股票代码\n",
    "    # Special step:根据文章的计算方法，计算个股每个月的ivol值，并放入month_ivol,同时将对应的股票代码放入mth_code\n",
    "    # 将每个月的month_ivol，mth_code放入大List中，最后再合并成dataframe\n",
    "    month_ivol = []\n",
    "    mth_code = []\n",
    "    # Step2: 提取当月的个股数据\n",
    "    for j in range(0,len(month_code)):\n",
    "        reg_df = [] # 将每只股票的回归数据dataframe放入reg_df中，跑回归时直接提取\n",
    "        daily_data_stk = daily_data.loc[month_code[j]] # 在某一个月内，按照month_code的顺序，提取个股数据\n",
    "        if daily_data_stk['Dretwd'].size >= 3: # 由于部分股票当月数据个数过少，故需要进行剔除样本个数<3的\n",
    "            x = daily_data_stk['Rm'] - daily_data_stk['Rf']   # 提取日度市场风险溢价Rm - Rf\n",
    "            y = daily_data_stk['Dretwd'] - daily_data_stk['Rf'] # 提取日度个股超额回报Ri - Rf\n",
    "            reg_data = pd.concat([y,x], axis=1) # 将回归数据拼成dataframe\n",
    "            reg_data.columns = ['y','x'] # 修改列名\n",
    "            reg_df.append(reg_data)\n",
    "            mth_code.append(month_code[j]) # 将个股当月的ivol因子对应的股票代码append到small list 中\n",
    "        else:None\n",
    "        # Step3: 计算每只个股在当月的ivol值\n",
    "        for k in range(0,len(reg_df)): # 提取reg_df中的回归数据\n",
    "            reg_data = reg_df[k]\n",
    "            est = smf.ols(formula='y ~ x', data=reg_data).fit() # 回归\n",
    "            ivol = np.std(est.resid) # 计算个股在当月的ivol因子\n",
    "            month_ivol.append(ivol) # 将ivol值append到small List中\n",
    "    IVOL.append(month_ivol)\n",
    "    Reg_stk_code.append(mth_code)\n",
    "toc = time.time()\n",
    "print(\"Minutes taken = \" + str((toc-tic)/60.0))   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "ab49d147",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将beta数据整理成dataframe格式\n",
    "IVOL_df = pd.DataFrame()\n",
    "for i in range(0,len(IVOL)):\n",
    "    df = pd.DataFrame()\n",
    "    df['beta'] = IVOL[i]\n",
    "    df['stk_code'] = Reg_stk_code[i]\n",
    "    df['Group'] = (i+1) * np.ones(len(IVOL[i]))\n",
    "    IVOL_df = pd.concat([IVOL_df,df], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "02c3f8a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "IVOL_df.columns = ['IVOL', 'stk_code', 'Group']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "f70c594e",
   "metadata": {},
   "outputs": [],
   "source": [
    "IVOL_df.to_csv('IVOL.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d92b73c8",
   "metadata": {},
   "source": [
    "### REV factor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "id": "d4ff0353",
   "metadata": {},
   "outputs": [],
   "source": [
    "rev = pd.read_csv('TRD_Mnth.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "id": "ebcb939a",
   "metadata": {},
   "outputs": [],
   "source": [
    "rev = rev.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "id": "f7dc7570",
   "metadata": {},
   "outputs": [],
   "source": [
    "rev = rev.drop(['Market_value','Volume'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "id": "b10d1147",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将股票代码前缺失的0补齐新生成一列数据，并替换原来的\"Stkcd\"\n",
    "stock_code_array = np.array(rev['Stkcd']) # 提取每个月在交易的股票代码，将其先转换成array\n",
    "stock_code = stock_code_array.tolist() # 而后将array转换成list\n",
    "stk_code = [] \n",
    "for i in stock_code:\n",
    "    code = str(i).zfill(6)\n",
    "    stk_code.append(code)\n",
    "rev['Stkcd'] = stk_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "id": "1dac8e9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "rev.index = rev['Trdmnt']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "id": "fef52560",
   "metadata": {},
   "outputs": [],
   "source": [
    "rev = rev.drop(rev.index[-1], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "id": "860d2004",
   "metadata": {},
   "outputs": [],
   "source": [
    "month = rev['Trdmnt'].drop_duplicates().tolist()\n",
    "month.sort()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "997c87e0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 将交易月份转换成group_number\n",
    "REV = pd.DataFrame()\n",
    "for i in range(0,len(month)):\n",
    "    df = rev.loc[month[i]]\n",
    "    df['Group'] = (i+1) * np.ones(len(df))\n",
    "    REV = pd.concat([REV,df],axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "id": "cf5d03fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "REV.to_csv('REV.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4cf44f0",
   "metadata": {},
   "source": [
    "### BM factor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f216f2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "''' bm数据预处理\n",
    "code = bm['order_book_id'].tolist()\n",
    "stk_code = []\n",
    "for i in code:\n",
    "    stk_code.append(i[0:6])\n",
    "bm['Stkcd'] = stk_code\n",
    "bm = bm.drop('order_book_id', axis=1)\n",
    "month = []\n",
    "date = bm['date'].tolist()\n",
    "for i in date:\n",
    "    month.append(i[0:7])\n",
    "bm['month'] = month\n",
    "bm = bm.drop('date', axis=1)\n",
    "bm.columns = ['bm_ratio','Stkcd','month']\n",
    "bm = bm.dropna()\n",
    "bm = bm.sort_values(by=['month','Stkcd'], ascending=True)\n",
    "bm.index = bm['Stkcd']\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e22a8eb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "bm = pd.read_csv('bm_process.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "193da94c",
   "metadata": {},
   "outputs": [],
   "source": [
    "trade_code = bm['Stkcd'].drop_duplicates().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d4aee21",
   "metadata": {},
   "outputs": [],
   "source": [
    "BM = pd.DataFrame()\n",
    "#Step1: 提取个股数据\n",
    "tic = time.time()\n",
    "for i in range(0,len(trade_code)):\n",
    "    print(i)\n",
    "    bm_data = bm.loc[trade_code[i]] # 提取个股的所有数据\n",
    "    bm_list = [] # 创建一个small list用于存放个股月度的bm\n",
    "    bm_df = pd.DataFrame() # 创建一个bm_df用与合并个股的bm、trade_code,trade_month\n",
    "    trade_month = bm_data['month'].drop_duplicates().tolist() # 提取个股的交易月份\n",
    "    bm_data.index = bm_data['month'] # 修改df的index为交易月份\n",
    "    #Step2: 按月份计算个股的bm_ratio，并放入small dataframe\n",
    "    for j in range(0,len(trade_month)):\n",
    "        bm_month_data = bm_data.loc[trade_month[j]] # 按月提取个股bm数据\n",
    "        bm_mean = bm_month_data['bm_ratio'].mean() # 计算个股月内bm均值\n",
    "        bm_list.append(bm_mean) # 将所得bm放入small list\n",
    "    bm_df['bm_ratio'] = bm_list # 合并个股的list数据\n",
    "    bm_df['month'] = trade_month\n",
    "    bm_df['Stkcd'] = bm_data.iloc[0:len(trade_month)]['Stkcd'].tolist()\n",
    "    #Step3: 将个股的dateframe与Big dataframe合并\n",
    "    BM = pd.concat([BM,bm_df], axis=0)\n",
    "toc = time.time()\n",
    "print(\"Minutes taken = \" + str((toc-tic)/60.0))   "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e40e3431",
   "metadata": {},
   "source": [
    "#### 上述BM原数据有问题，更换数据后重新编写BM factor代码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 421,
   "id": "5d79bce7",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_excel('bm.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 422,
   "id": "83a36ca6",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 440,
   "id": "3d7546f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "Data = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 452,
   "id": "4bf9dd8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "Data = pd.concat([Data,data], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 454,
   "id": "3fff1e2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "Data = Data.sort_values(by=['Stk','year'], ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 463,
   "id": "a08a73f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "year = Data['year'].drop_duplicates().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 467,
   "id": "19162364",
   "metadata": {},
   "outputs": [],
   "source": [
    "stk_code = Data['Stk'].drop_duplicates().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 471,
   "id": "addc07cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = Data.loc[stk_code[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 474,
   "id": "f09707c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.index = df['year']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 526,
   "id": "6f336e54",
   "metadata": {},
   "outputs": [],
   "source": [
    "mth_df = df.loc[year[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 528,
   "id": "c091dd10",
   "metadata": {},
   "outputs": [],
   "source": [
    "mth_df.index = Month[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42767ade",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step1: 先提取个股的所有数据\n",
    "bm_df = pd.DataFrame()\n",
    "tic = time.time()\n",
    "for i in range(0,len(stk_code)):\n",
    "    print(i)\n",
    "    df = Data.loc[stk_code[i]]\n",
    "    df.index = df['year']\n",
    "    year = df['year'].drop_duplicates().tolist() # 提取个股的交易年份\n",
    "    year.sort()\n",
    "    Month = [] # 将对应的交易年份转换为交易月份\n",
    "    for n in range(0,len(year)):\n",
    "        month = []\n",
    "        for h in range(1,13):\n",
    "            if h <=9:\n",
    "                m = year[n][0:5] + '0' + str(h)\n",
    "            else:\n",
    "                m = year[n][0:5]  + str(h)\n",
    "            month.append(m)\n",
    "        Month.append(month)\n",
    "# Step2: 依次提取个股的年度数据并修改其index，再合并到DataFrame中\n",
    "    for j in range(0,len(Month)):\n",
    "        mth_df = df.loc[year[j]]\n",
    "        mth_df.index = Month[j]\n",
    "        bm_df = pd.concat([bm_df,mth_df], axis=0)\n",
    "toc = time.time()\n",
    "print(\"Minutes taken = \" + str((toc-tic)/60.0))   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 585,
   "id": "daede777",
   "metadata": {},
   "outputs": [],
   "source": [
    "bm_df.to_csv('bm_process.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 588,
   "id": "97c4f60d",
   "metadata": {},
   "outputs": [],
   "source": [
    "bm_df['month'] = bm_df.index\n",
    "bm_df = bm_df.drop('year', axis=1)\n",
    "list_month = bm_df['month'].drop_duplicates().tolist() # 提取交易月份数据\n",
    "list_month.sort()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 594,
   "id": "06dae827",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将交易月份依次替换成Group_number\n",
    "BM = pd.DataFrame()\n",
    "for i in range(0,len(list_month)):\n",
    "    df = bm_df.loc[list_month[i]]\n",
    "    df.index = (i+1) * np.ones(len(df))\n",
    "    BM = pd.concat([BM,df],axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 596,
   "id": "805aec10",
   "metadata": {},
   "outputs": [],
   "source": [
    "BM['Group'] = BM.index-1\n",
    "BM = BM.drop(BM.index[0], axis=0)\n",
    "BM.index = BM['Group']\n",
    "BM.to_csv('bm_process.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 609,
   "id": "c76537dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "BM.index = BM['month']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 610,
   "id": "3a8d2119",
   "metadata": {},
   "outputs": [],
   "source": [
    "Fac_reg = pd.merge(Fac,BM, on=['Stk','Group'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 613,
   "id": "65072c5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "Fac_reg.to_csv('Fac_reg_2.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50a2bba6",
   "metadata": {},
   "source": [
    "###  Factor Merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "id": "2170d5d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "MAX = pd.read_csv('MAX.csv')\n",
    "BETA = pd.read_csv('BETA.csv')\n",
    "IVOL = pd.read_csv('IVOL.csv')\n",
    "REV = pd.read_csv('REV.csv')\n",
    "SIZE = pd.read_csv('SIZE.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "id": "ef318624",
   "metadata": {},
   "outputs": [],
   "source": [
    "fac_list = [MAX, BETA,ILLIQ,IVOL,MOM,REV,SIZE,BM]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "id": "c5769027",
   "metadata": {},
   "outputs": [],
   "source": [
    "BETA = BETA.drop(['Unnamed: 0'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "id": "adcbfb1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "IVOL = IVOL.drop(['Unnamed: 0'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "id": "c782740b",
   "metadata": {},
   "outputs": [],
   "source": [
    "SIZE = SIZE.drop(['Unnamed: 0'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "id": "5eb2e3a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "SIZE['size'] = np.log(SIZE['Msmvosd'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b59c188",
   "metadata": {},
   "outputs": [],
   "source": [
    "SIZE.drop('Msmvosd', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70874596",
   "metadata": {},
   "source": [
    "#### 处理ILLIQ factor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "id": "a65dba11",
   "metadata": {},
   "outputs": [],
   "source": [
    "illiq = pd.read_csv('ILLIQ.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "id": "5421fdf0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ILLIQ factor 将交易月份转换成group_number\n",
    "month = illiq['month'].drop_duplicates().tolist()\n",
    "month.sort()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "id": "b737f354",
   "metadata": {},
   "outputs": [],
   "source": [
    "illiq.index = illiq['month']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f38f2d5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将交易月份转换成group_number\n",
    "ILLIQ = pd.DataFrame()\n",
    "for i in range(0,len(month)):\n",
    "    df = illiq.loc[month[i]]\n",
    "    df['Group'] = (i+1) * np.ones(len(df))\n",
    "    ILLIQ = pd.concat([ILLIQ,df],axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "276339f5",
   "metadata": {},
   "source": [
    "#### 处理 MOM factor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "id": "d06167cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "mom = pd.read_csv('MOM.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "id": "add39366",
   "metadata": {},
   "outputs": [],
   "source": [
    "mom = mom.drop('Unnamed: 0', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "id": "39e0a186",
   "metadata": {},
   "outputs": [],
   "source": [
    "mom.index = mom['month']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "id": "c5e11c05",
   "metadata": {},
   "outputs": [],
   "source": [
    "mom = mom.drop(mom.index[0], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "id": "0ff3c362",
   "metadata": {},
   "outputs": [],
   "source": [
    "month = mom['month'].drop_duplicates().tolist()\n",
    "month.sort()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aac3dc47",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将交易月份转换成group_number\n",
    "MOM = pd.DataFrame()\n",
    "for i in range(0,len(month)):\n",
    "    df = mom.loc[month[i]]\n",
    "    df['Group'] = (i+1) * np.ones(len(df))\n",
    "    MOM = pd.concat([MOM,df],axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "id": "f50513c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "MAX.columns = ['MAX','Group','Stk','MAX_1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "id": "fbc5d820",
   "metadata": {},
   "outputs": [],
   "source": [
    "BETA.columns = ['BETA','Stk','Group']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 297,
   "id": "3809fb23",
   "metadata": {},
   "outputs": [],
   "source": [
    "ILLIQ.columns = ['Stk','month','ILLIQ','Group']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "id": "cb8e8076",
   "metadata": {},
   "outputs": [],
   "source": [
    "IVOL.columns = ['IVOL','Stk','Group']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 301,
   "id": "35982ec8",
   "metadata": {},
   "outputs": [],
   "source": [
    "MOM.columns = ['MOM','month','Stk','Group']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "id": "bca5867d",
   "metadata": {},
   "outputs": [],
   "source": [
    "MOM = MOM.drop('month', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "id": "864c192b",
   "metadata": {},
   "outputs": [],
   "source": [
    "REV.columns = ['Stk','month','REV','Group']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 308,
   "id": "f093a2c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "REV = REV.drop('month', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "id": "96eab9b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "SIZE.columns = ['Stk','month','Msmvosd','Group','size']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "id": "727a5407",
   "metadata": {},
   "outputs": [],
   "source": [
    "SIZE = SIZE.drop(['month','Msmvosd'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "id": "fd3f30ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将股票代码前缺失的0补齐新生成一列数据，并替换原来的\"Stkcd\"\n",
    "stock_code_array = np.array(SIZE['Stk']) # 提取每个月在交易的股票代码，将其先转换成array\n",
    "stock_code = stock_code_array.tolist() # 而后将array转换成list\n",
    "stk_code = [] \n",
    "for i in stock_code:\n",
    "    code = str(i).zfill(6)\n",
    "    stk_code.append(code)\n",
    "SIZE['Stk'] = stk_code"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff0508ac",
   "metadata": {},
   "source": [
    "#### Merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 329,
   "id": "6b4e2214",
   "metadata": {},
   "outputs": [],
   "source": [
    "Fac_set = pd.merge(Fac_set,SIZE,on=['Group','Stk'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 333,
   "id": "3f38aced",
   "metadata": {},
   "outputs": [],
   "source": [
    "Fac_set.to_csv('Factor_set.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 335,
   "id": "ab067e3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "Fac_set.index = Fac_set['Group']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 344,
   "id": "7c50016c",
   "metadata": {},
   "outputs": [],
   "source": [
    "group_list = Fac_set['Group'].drop_duplicates().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 372,
   "id": "6e1d2250",
   "metadata": {},
   "outputs": [],
   "source": [
    "Fac_set.index = Fac_set['Group']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 376,
   "id": "7e576da0",
   "metadata": {},
   "outputs": [],
   "source": [
    "Fac_reg = Fac_set.sort_values(by=['Stk','month'], ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 378,
   "id": "9c28c21e",
   "metadata": {},
   "outputs": [],
   "source": [
    "Fac_reg.to_csv('Fac_reg.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
